CREATE DATABASE [PrototypeTest]  ON (NAME = N'PrototypeTest_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\PrototypeTest_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'PrototypeTest_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\PrototypeTest_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'PrototypeTest', N'autoclose', N'false'
GO

exec sp_dboption N'PrototypeTest', N'bulkcopy', N'false'
GO

exec sp_dboption N'PrototypeTest', N'trunc. log', N'false'
GO

exec sp_dboption N'PrototypeTest', N'torn page detection', N'true'
GO

exec sp_dboption N'PrototypeTest', N'read only', N'false'
GO

exec sp_dboption N'PrototypeTest', N'dbo use', N'false'
GO

exec sp_dboption N'PrototypeTest', N'single', N'false'
GO

exec sp_dboption N'PrototypeTest', N'autoshrink', N'false'
GO

exec sp_dboption N'PrototypeTest', N'ANSI null default', N'false'
GO

exec sp_dboption N'PrototypeTest', N'recursive triggers', N'false'
GO

exec sp_dboption N'PrototypeTest', N'ANSI nulls', N'false'
GO

exec sp_dboption N'PrototypeTest', N'concat null yields null', N'false'
GO

exec sp_dboption N'PrototypeTest', N'cursor close on commit', N'false'
GO

exec sp_dboption N'PrototypeTest', N'default to local cursor', N'false'
GO

exec sp_dboption N'PrototypeTest', N'quoted identifier', N'false'
GO

exec sp_dboption N'PrototypeTest', N'ANSI warnings', N'false'
GO

exec sp_dboption N'PrototypeTest', N'auto create statistics', N'true'
GO

exec sp_dboption N'PrototypeTest', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'PrototypeTest', N'db chaining', N'false'
GO

use [PrototypeTest]
GO

CREATE TABLE [dbo].[Child] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ParentID] [int] NOT NULL ,
	[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Parent] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Child] ADD 
	CONSTRAINT [PK_Child] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Parent] ADD 
	CONSTRAINT [PK_Parent] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Child] ADD 
	CONSTRAINT [FK_Child_Parent] FOREIGN KEY 
	(
		[ParentID]
	) REFERENCES [dbo].[Parent] (
		[ID]
	)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure AddChild(@parentID int, @firstName varchar(50), @lastName varchar(50))
as
begin
	set nocount on
	if (not exists(select * from Child where FirstName = @firstName and LastName = @lastName))
		insert into Child (ParentID, FirstName, LastName) values(@parentID, @firstName, @lastName)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure AddParent(@firstName varchar(50), @lastName varchar(50))
as
begin
	set nocount on
	if (not exists(select * from Parent where FirstName = @firstName and LastName = @lastName))
		insert into Parent (FirstName, LastName) values(@firstName, @lastName)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure DeleteChild(@id int)
as
begin
	set nocount on
	delete from Child where [ID] = @id
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure DeleteParent(@id int)
as
begin
	set nocount on
	delete from Parent where [ID] = @id
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure GetAllChildren
as
begin
	set nocount on
	select * from Child
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure GetAllParents
as
begin
	set nocount on
	select * from Parent
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure GetChildren(@parentID int)
as
begin
	set nocount on
	select * from Child c where c.ParentID = @parentID
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

create procedure GetParent(@parentID int)
as
begin
	set nocount on
	select * from Parent where [ID] = @parentID
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure UpdateChild(@id int, @parentID int, @firstName varchar(50), @lastName varchar(50))
as
begin
	set nocount on
	update Child  set ParentID = @parentID, FirstName = @firstName, LastName = @lastName where [ID] = @id
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure UpdateParent(@id int, @firstName varchar(50), @lastName varchar(50))
as
begin
	set nocount on
	update Parent set FirstName = @firstName, LastName = @lastName where [ID] = @id
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

